import 'package:flutter01/pages/sqfLite/utils/object_util.dart';
import 'package:flutter01/pages/sqfLite/models/note.dart';
import 'package:flutter01/pages/sqfLite/sqflite_providers/base_table_provider.dart';
import 'package:flutter01/pages/sqfLite/utils/date_time.dart';
import 'package:sqflite/sqflite.dart';

class NoteTableProvider extends BaseTableProvider {
  NoteTableProvider([tableName = "note"]) : super(tableName);

  //表列名
  final String columnId = "id";
  final String columnTargetId = "target_id";
  final String columnNote = "note";
  final String columnCreateTime = "n_create_time";

  @override
  String createTableString() {
    return '''CREATE TABLE $tableName(
    $columnId INTEGER PRIMARY KEY AUTOINCREMENT,
    $columnTargetId INTEGER NOT NULL,
    $columnNote TEXT NOT NULL,
    $columnCreateTime TEXT NOT NULL)''';
  }

// 新增一条日记 insert
  Future insertNote(Note note) async {
    Database db = await getDataBase();
    int targetId = note.targetId!;
    String noteText = note.note!;
    String? createTime = formatTime(dateTime: note.createTime!);
    if (ObjectUtil.isEmptyString(createTime)) return;

    //执行插入操作
    return await db.transaction((txn) async {
      int rowId = await txn.rawInsert(
          'INSET INTO $tableName($columnTargetId,$columnNote,$columnCreateTime) VALUES($targetId,$noteText,$createTime)');
      return rowId;
    });
  }

// 查询某条目标下的所有日记 select
Future<List<Note>> queryNotesByTarget({required int targetId}) async {
  Database db = await getDataBase();
  String sql = 'SELECT * FROM $tableName WHERE $columnTargetId = $targetId ORDER BY $columnCreateTime';
  List<Map<String,dynamic>> results = await db.rawQuery(sql);
  List<Note> notes = [];
  results.forEach((element) {
    Note? note = Note.noteFromMap(element);
    if(note != null){
      notes.add(note);
    }
  });
  return notes;
}

}
